''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：pymysqlModel.py
#日期：2018-10-22
#备注：pip install pymysql  pymysql是Python中操作MySQL的模块   F:\python_env\PaChong_env
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import xlrd
import xlwt
import os
from xlutils.copy import copy
from pycacho.cachobase.deal_time import minus_date
from pycacho.cachomodule.zipfile_model import write_file


# 发票模板：需要修改的地方（定位发票具体要修改的地方）
def invoice(sample_file):
    data = xlrd.open_workbook(sample_file)
    table = data.sheets()[0]
    a = table.cell_value(7, 1)
    b = table.cell_value(8, 5)
    print(a, b)


# 获取发票需要的动态参数：第3行第2列B发票号invoice，第3行第8列H姓名name
def get_excel_data(packing_file, invoice_cell, name_cell, address_cell):
    data = xlrd.open_workbook(packing_file)
    table = data.sheets()[0]
    n_rows = table.nrows
    num = 0
    data = []
    for i in range(0, n_rows):  # 第三行
        row = []
        invoice_no = table.cell_value(i, invoice_cell)
        name = table.cell_value(i, name_cell)
        address = table.cell_value(i, address_cell)
        if invoice_no != '':
            num = num + 1
            row.append(invoice_no)
            row.append(name)
            row.append(address)
            data.append(row)
    print('总发票数量：', num)
    return data


# TypeError: descriptor 'decode' requires a 'bytes' object but received a 'NoneType'
# F:\python_env\PaChong_env\lib\site-packages\\xlwt\UnicodeUtils.py
def make_excel(path, invoice_no, name, address):
    width = 256 * 8  # 8个字符宽
    # 字体和格式
    font = xlwt.Font()
    font.height = 240  # 12号字体
    font.bold = True
    font.name = 'Times New Roman'
    style = xlwt.XFStyle()
    style.font = font
    names = ['Eucalipto', 'Henry', 'Manolo', 'Vizcainas', 'LUIS', 'JOSE']
    zip_file = path + minus_date(0, 's') + '.zip'
    if os.path.exists(zip_file):
        os.remove(zip_file)
    for invoice_type in names:
        if invoice_type in address:
            sample_file = path + 'invoice_sample_' + invoice_type + '.xls'
            invoice_name = invoice_type + '.xls'
            invoice = path + invoice_no + ' ' + invoice_name
            date = '2020' + '-' + invoice_no[3:5] + '-' + invoice_no[5:7]
            data = xlrd.open_workbook(sample_file, formatting_info=True)
            new_excel = copy(data)
            ws = new_excel.get_sheet(0)  # 获取第一个sheet
            first_col = ws.col(0)  # 第一列
            first_col.width = width  # 第一列宽
            ws.write(4, 8, date, style)  # 5I(4,8)
            ws.write(8, 1, name, style)  # B9(9,1)
            ws.write(9, 8, invoice_no, style)  # I10(10,9)
            new_excel.save(invoice)
            write_file(zip_file, invoice, os.path.basename(invoice))
    # print("发票完成！",zip_file)


def mk_invoices(packing_file, invoice_cell, name_cell, address_cell):
    path = os.path.dirname(packing_file) + '\\'
    data = get_excel_data(packing_file, invoice_cell, name_cell, address_cell)  # 获取所需参数
    for info in data:
        invoice_no = info[0]
        name = info[1]
        address = info[2]
        # print(invoice_no,name)
        make_excel(path, invoice_no, name, address)
